package da;

import data.User;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.ResourceBundle;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Groep 11
 * @date 2007-2008
 */
public class UserDA {

    private ResourceBundle bundle;
    private static UserDA instance = null;

    private UserDA() {
        bundle = ResourceBundle.getBundle("da.dbconsts");
    }

    public static UserDA getInstance() {
        if (instance == null) {
            instance = new UserDA();
        }
        return instance;
    }

    // <editor-fold desc="SELECTIONS" defaultstate="collapsed">    
    /**
     * Fetches a user by userId.
     * Returns null if no user was found.
     * @param userId
     * @return user
     * 
     */
    public User getUserById(int userId) {
        User user = null;

        String sql = bundle.getString("getUserById");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setInt(1, userId);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    user = new User(userId, rs.getString("last_name"),
                            rs.getString("first_name"), rs.getString("username"),
                            rs.getString("password"), rs.getString("email"), rs.getString("country"),
                            rs.getBoolean("active"));

                    int tribeId = getTribeIdByUserId(rs.getInt("user_id"));
                    user.setTribeId(tribeId);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getUserById error -->" + ex.toString());
        }
        return user;

    }

    /**
     * Fetches a user by username.
     * Returns null if no user was found.
     * @param username
     * @return user
     */
    public User getUserByName(String username) {
        User user = null;

        String sql = bundle.getString("getUserByName");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, username);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    user = new User(rs.getInt("user_id"), rs.getString("last_name"),
                            rs.getString("first_name"), rs.getString("username"),
                            rs.getString("password"), rs.getString("email"), rs.getString("country"),
                            rs.getBoolean("active"));

                    int tribeId = getTribeIdByUserId(rs.getInt("user_id"));
                    user.setTribeId(tribeId);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getUserByName error -->" + ex.toString());
        }
        return user;

    }

    /**
     * Fetches a user by username and password
     * Returns null if no user was found.
     * @param username
     * @param password
     * @return user
     */
    public User getUserByUsernamePassword(String username, String password) {
        User user = null;

        String sql = bundle.getString("getUserByUsernamePassword");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, username);
                prepstat.setString(2, password);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    user = new User(rs.getInt("user_id"), rs.getString("last_name"),
                            rs.getString("first_name"), rs.getString("username"),
                            rs.getString("password"), rs.getString("email"), rs.getString("country"),
                            rs.getBoolean("active"));

                    int tribeId = getTribeIdByUserId(rs.getInt("user_id"));
                    user.setTribeId(tribeId);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getUserByUsernamePassword error -->" + ex.toString());
        }
        return user;
    }

    /**
     * Fetches a list of friends of a given user.
     * @param username
     * @return list of users
     */
    public Vector<User> getFriendsOfUser(String username) {
        Vector<User> friends = null;
        User user = null;

        int id = getUserIdByUsername(username);
        String sql = bundle.getString("getFriendsOfUserById");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setInt(1, id);
                prepstat.setInt(2, id);
                prepstat.setInt(3, id);

                ResultSet rs = prepstat.executeQuery();

                friends = new Vector<User>();
                while (rs.next()) {
                    user = new User(rs.getInt("user_id"), rs.getString("last_name"),
                            rs.getString("first_name"), rs.getString("username"),
                            rs.getString("password"), rs.getString("email"), rs.getString("country"),
                            rs.getBoolean("active"));

                    int tribeId = getTribeIdByUserId(rs.getInt("user_id"));
                    user.setTribeId(tribeId);

                    friends.add(user);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getFriendsOfUser error -->" + ex.toString());
        }


        return friends;
    }

    /**
     * Fetches a list with usernames of a given user's friends.
     * @param username
     * @return friend's usernames
     */
    public Vector<String> getFriendsUsernamesOfUser(String username) {
        Vector<String> usernames = null;
        String sFound = null;

        int id = getUserIdByUsername(username);
        String sql = bundle.getString("getFriendsUsernamesOfUserById");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setInt(1, id);
                prepstat.setInt(2, id);
                prepstat.setInt(3, id);

                ResultSet rs = prepstat.executeQuery();

                usernames = new Vector<String>();
                while (rs.next()) {
                    sFound = rs.getString("username");

                    usernames.add(sFound);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getFriendsUsernamesOfUser error -->" + ex.toString());
        }


        return usernames;
    }

    /**
     * Fetches a list with usernames of a given user's colleage tribe members.
     * @param username
     * @return
     */
    public Vector<User> getTribeMembersOfUser(String username) {
        Vector<User> tribeMembers = null;
        Vector<Integer> ids = null;
        User tribeMember = null;

        int id = getUserIdByUsername(username);
        String sql_ids = bundle.getString("getTribeMembersOfUserById");
        String sql_names = bundle.getString("getUserById");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql_ids);
            try {
                // Fetch all id's of colleage tribe members
                prepstat.setInt(1, id);
                prepstat.setInt(2, id);
                ResultSet rs = prepstat.executeQuery();

                ids = new Vector<Integer>();
                while (rs.next()) {
                    int g = rs.getInt("user_id");
                    ids.add(g);
                }

                // Fetch users for the given id's
                tribeMembers = new Vector<User>();
                for (int i : ids) {
                    prepstat = dbcon.prepareStatement(sql_names);
                    prepstat.setInt(1, i);
                    rs = prepstat.executeQuery();


                    while (rs.next()) {
                        tribeMember = new User(rs.getInt("user_id"), rs.getString("last_name"),
                                rs.getString("first_name"), rs.getString("username"),
                                rs.getString("password"), rs.getString("email"), rs.getString("country"),
                                rs.getBoolean("active"));

                        int tribeId = getTribeIdByUserId(rs.getInt("user_id"));
                        tribeMember.setTribeId(tribeId);

                        tribeMembers.add(tribeMember);
                    }
                }


            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getFriendsUsernamesOfUser error -->" + ex.toString());
        }


        return tribeMembers;
    }

    /**
     * Fetches the id bound to a given username.
     * @param username
     * @return id
     */
    public int getUserIdByUsername(String username) {
        int id = 0;

        String sql = bundle.getString("getUserIdByUsername");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, username);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    id = rs.getInt("user_id");
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getUserIdByUsername error -->" + ex.toString());
        }

        return id;
    }

    /*
     * Fetches the tribeId of a given userId 
     */
    private int getTribeIdByUserId(int userId) {
        int tribeId = 1;

        String sql = bundle.getString("getTribeIdByUserId");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setInt(1, userId);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    tribeId = rs.getInt("tribe_id");
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getTribeIdByUserId error -->" + ex.toString());
        }

        return tribeId;
    }

    public Vector<User> getAllUsers() {
        
        Vector<User> users = null;

        String sql = bundle.getString("getAllUsers");
        Connection dbcon = null;

        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                ResultSet rs = prepstat.executeQuery();

                users = new Vector<User>();
                while (rs.next()) {
                   User user = new User(rs.getInt("user_id"), rs.getString("last_name"),
                            rs.getString("first_name"), rs.getString("username"),
                            rs.getString("password"), rs.getString("email"), rs.getString("country"),
                            rs.getBoolean("active"));

                    users.add(user);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getFriendsUsernamesOfUser error -->" + ex.toString());
        }


        return users;
    }

    // </editor-fold>
    // <editor-fold desc="INSERTS" defaultstate="collapsed">
    /**
     * Adds a user to the DB.
     * 
     * First, the user is added to the user table, next the user's added
     * to tribe "no tribe".
     * @param user
     */
    public void addUser(User user) {

        String sqlUser = bundle.getString("addUser");
        String sqlTribeMember = bundle.getString("addUserToDefaultTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sqlUser);
            try {
                // Add data to table "user"
                prepstat.setString(1, user.getLastName());
                prepstat.setString(2, user.getFirstName());
                prepstat.setString(3, user.getUsername());
                prepstat.setString(4, user.getPassword());
                prepstat.setString(5, user.getEmail());
                prepstat.setString(6, user.getCountry());
                prepstat.setBoolean(7, user.isActive());
                prepstat.executeUpdate();


                // Fetch the actual id from the DB
                // Bind this id to the appropriate tribe in table "tribemember"
                int id = getUserIdByUsername(user.getUsername());
                prepstat = dbcon.prepareStatement(sqlTribeMember);
                prepstat.setInt(1, id);
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("addUser error -->" + ex.toString());
        }


    }

    // </editor-fold>
    // <editor-fold desc="UPDATES" defaultstate="collapsed">
    /**
     * Updates an existing user. 
     * @param user
     */
    public void editUser(User user) {

        String sqlUser = bundle.getString("editUser");
        String sqlTribeMember = bundle.getString("editMemberOfTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sqlUser);
            try {
                // Updates table "user"
                prepstat.setString(1, user.getLastName());
                prepstat.setString(2, user.getFirstName());
                prepstat.setString(3, user.getUsername());
                prepstat.setString(4, user.getPassword());
                prepstat.setString(5, user.getEmail());
                prepstat.setString(6, user.getCountry());
                prepstat.setBoolean(7, user.isActive());
                prepstat.setInt(8, user.getUserId());
                prepstat.executeUpdate();

                // Updates table "tribemember"
                prepstat = dbcon.prepareStatement(sqlTribeMember);
                prepstat.setInt(1, user.getTribeId());
                prepstat.setInt(2, user.getUserId());
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("editUser error -->" + ex.toString());
        }
    }

    // </editor-fold>
    // <editor-fold desc="DELETES" defaultstate="collapsed">
    /**
     * Removes a given user from the DB
     * @param userId
     */
    public void removeUser(int userId) {

        String sqlUser = bundle.getString("removeUser");
        String sqlTribeMember = bundle.getString("removeMemberFromTribe");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sqlUser);
            try {
                // Delete from table "user"
                prepstat.setInt(1, userId);
                prepstat.executeUpdate();

                // Delete from table "tribemember"
                prepstat = dbcon.prepareStatement(sqlTribeMember);
                prepstat.setInt(1, userId);
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("removeUser error -->" + ex.toString());
        }
    }
    // </editor-fold>
}
